<?php
    class Default_Model_Article extends Zend_Db_Table_Abstract
    {
        protected $_db;
		
		public function __construct()
		{
			$this->_db = Zend_Registry::get('db');
		}
        
        public function listItem($locale)
        {
            $article = array(
                'id',
                'location_id',
                'image',
            );
            $article_language = array(
                'title',
                'short_description',
            );
            $sql = $this->_db->select()
                            ->from('article AS h', $article)
                            ->join('article_language AS hl', 'hl.article_id = h.id', $article_language)
                            ->join('language AS l', 'l.id = hl.language_id', null)
                            ->where('h.status = 1')
                            ->where('l.locale = ?', $locale)
                            ->order('h.id DESC');
            return $sql;
        }
        
        public function getItem($id, $locale)
        {
            $article = array(
                'location_id',
                'create_date',
            );
            $article_language = array(
                'title',
                'description',
            );
            $sql = $this->_db->select()
                            ->from('article AS a', $article)
                            ->join('article_language AS al', 'al.article_id = a.id', $article_language)
                            ->join('language AS l', 'l.id = al.language_id', null)
                            ->where('a.id = ?', $id)
                            ->where('a.status = 1')
                            ->where('l.locale = ?', $locale);
            $result = $this->_db->fetchRow($sql);
            return $result;
        }
        
        public function getItemByLocation($data, $locale)
        {
            $article = array(
                'id',
            );
            $article_language = array(
                'title',
            );
            $sql = $this->_db->select()
                            ->from('article AS a', $article)
                            ->join('article_language AS al', 'al.article_id = a.id', $article_language)
                            ->join('language AS l', 'l.id = al.language_id', null)
                            ->where('a.location_id = ?', $data['location_id'])
                            ->where('a.id <> ?', $data['id'])
                            ->where('a.status = 1')
                            ->where('l.locale = ?', $locale)
                            ->limit(5);
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function getListItemByLocation($location_id, $locale)
        {
            $article = array(
                'id',
                'location_id',
                'image',
            );
            $article_language = array(
                'title',
                'short_description',
            );
            $sql = $this->_db->select()
                            ->from('article AS h', $article)
                            ->join('article_language AS hl', 'hl.article_id = h.id', $article_language)
                            ->join('language AS l', 'l.id = hl.language_id', null)
                            ->where('h.status = 1')
                            ->where('l.locale = ?', $locale)
                            ->where('h.location_id = ?', $location_id)
                            ->order('h.id DESC');
            return $sql;
        }
    }
